#include "course_manager.h"
#include "database.h"
#include <iostream>
#include <sstream>

CourseManager::CourseManager(Database* db) : db_(db) {}

CourseManager::~CourseManager() = default;

std::vector<Course> CourseManager::getAllCourses() {
    std::vector<Course> courses;

    std::string sql = R"(
        SELECT c.id, c.course_code, c.course_name, c.teacher_id, t.name as teacher_name,
               c.description, c.credits, c.max_students, c.current_students,
               c.semester, c.classroom, c.schedule, c.status
        FROM courses c
        LEFT JOIN teachers t ON c.teacher_id = t.id
        ORDER BY c.created_at DESC
    )";

    auto result = db_->query(sql);
    if (!result) {
        return courses;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        Course course;
        course.id = std::stoi(row[0]);
        course.course_code = row[1] ? row[1] : "";
        course.course_name = row[2] ? row[2] : "";
        course.teacher_id = row[3] && std::string(row[3]).length() > 0 ? std::stoi(row[3]) : 0;
        course.teacher_name = row[4] ? row[4] : "";
        course.description = row[5] ? row[5] : "";
        course.credits = row[6] && std::string(row[6]).length() > 0 ? std::stoi(row[6]) : 0;
        course.max_students = row[7] && std::string(row[7]).length() > 0 ? std::stoi(row[7]) : 0;
        course.current_students = row[8] && std::string(row[8]).length() > 0 ? std::stoi(row[8]) : 0;
        course.semester = row[9] ? row[9] : "";
        course.classroom = row[10] ? row[10] : "";
        course.schedule = row[11] ? row[11] : "";
        course.status = row[12] ? row[12] : "";
        courses.push_back(course);
    }

    return courses;
}

std::optional<Course> CourseManager::getCourseById(int courseId) {
    std::stringstream ss;
    ss << "SELECT c.id, c.course_code, c.course_name, c.teacher_id, t.name as teacher_name, "
       << "c.description, c.credits, c.max_students, c.current_students, "
       << "c.semester, c.classroom, c.schedule, c.status "
       << "FROM courses c LEFT JOIN teachers t ON c.teacher_id = t.id WHERE c.id = " << courseId;

    auto result = db_->query(ss.str());
    if (!result) {
        return std::nullopt;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    if (!row) {
        return std::nullopt;
    }

    Course course;
    course.id = std::stoi(row[0]);
    course.course_code = row[1] ? row[1] : "";
    course.course_name = row[2] ? row[2] : "";
    course.teacher_id = row[3] ? std::stoi(row[3]) : 0;
    course.teacher_name = row[4] ? row[4] : "";
    course.description = row[5] ? row[5] : "";
    course.credits = std::stoi(row[6]);
    course.max_students = std::stoi(row[7]);
    course.current_students = std::stoi(row[8]);
    course.semester = row[9] ? row[9] : "";
    course.classroom = row[10] ? row[10] : "";
    course.schedule = row[11] ? row[11] : "";
    course.status = row[12] ? row[12] : "";
    return course;
}

bool CourseManager::courseExists(int courseId) {
    std::stringstream ss;
    ss << "SELECT COUNT(*) FROM courses WHERE id = " << courseId;

    auto result = db_->query(ss.str());
    if (!result) {
        return false;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    if (!row) {
        return false;
    }

    return std::stoi(row[0]) > 0;
}

std::optional<Student> CourseManager::getStudentByUserId(int userId) {
    std::stringstream ss;
    ss << "SELECT id, user_id, student_id, name, gender, class_name, email, phone "
       << "FROM students WHERE user_id = " << userId;

    auto result = db_->query(ss.str());
    if (!result) {
        return std::nullopt;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    if (!row) {
        return std::nullopt;
    }

    Student student;
    student.id = std::stoi(row[0]);
    student.user_id = std::stoi(row[1]);
    student.student_id = row[2] ? row[2] : "";
    student.name = row[3] ? row[3] : "";
    student.gender = row[4] ? row[4] : "";
    student.class_name = row[5] ? row[5] : "";
    student.email = row[6] ? row[6] : "";
    student.phone = row[7] ? row[7] : "";
    return student;
}

std::vector<Course> CourseManager::getStudentCourses(int studentId) {
    std::vector<Course> courses;

    std::stringstream ss;
    ss << "SELECT c.id, c.course_code, c.course_name, c.teacher_id, t.name as teacher_name, "
       << "c.description, c.credits, c.max_students, c.current_students, "
       << "c.semester, c.classroom, c.schedule, c.status "
       << "FROM courses c "
       << "LEFT JOIN teachers t ON c.teacher_id = t.id "
       << "INNER JOIN course_selections cs ON c.id = cs.course_id "
       << "WHERE cs.student_id = " << studentId << " AND cs.status = '已选'";

    auto result = db_->query(ss.str());
    if (!result) {
        return courses;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        Course course;
        course.id = std::stoi(row[0]);
        course.course_code = row[1] ? row[1] : "";
        course.course_name = row[2] ? row[2] : "";
        course.teacher_id = row[3] && std::string(row[3]).length() > 0 ? std::stoi(row[3]) : 0;
        course.teacher_name = row[4] ? row[4] : "";
        course.description = row[5] ? row[5] : "";
        course.credits = row[6] && std::string(row[6]).length() > 0 ? std::stoi(row[6]) : 0;
        course.max_students = row[7] && std::string(row[7]).length() > 0 ? std::stoi(row[7]) : 0;
        course.current_students = row[8] && std::string(row[8]).length() > 0 ? std::stoi(row[8]) : 0;
        course.semester = row[9] ? row[9] : "";
        course.classroom = row[10] ? row[10] : "";
        course.schedule = row[11] ? row[11] : "";
        course.status = row[12] ? row[12] : "";
        courses.push_back(course);
    }

    return courses;
}

std::vector<Student> CourseManager::getAllStudents() {
    std::vector<Student> students;

    std::string sql = "SELECT id, user_id, student_id, name, gender, class_name, email, phone FROM students ORDER BY id";

    auto result = db_->query(sql);
    if (!result) {
        return students;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        Student student;
        student.id = std::stoi(row[0]);
        student.user_id = std::stoi(row[1]);
        student.student_id = row[2] ? row[2] : "";
        student.name = row[3] ? row[3] : "";
        student.gender = row[4] ? row[4] : "";
        student.class_name = row[5] ? row[5] : "";
        student.email = row[6] ? row[6] : "";
        student.phone = row[7] ? row[7] : "";
        students.push_back(student);
    }

    return students;
}

std::optional<Teacher> CourseManager::getTeacherByUserId(int userId) {
    std::stringstream ss;
    ss << "SELECT id, user_id, teacher_id, name, department, email, phone "
       << "FROM teachers WHERE user_id = " << userId;

    auto result = db_->query(ss.str());
    if (!result) {
        return std::nullopt;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    if (!row) {
        return std::nullopt;
    }

    Teacher teacher;
    teacher.id = std::stoi(row[0]);
    teacher.user_id = std::stoi(row[1]);
    teacher.teacher_id = row[2] ? row[2] : "";
    teacher.name = row[3] ? row[3] : "";
    teacher.department = row[4] ? row[4] : "";
    teacher.email = row[5] ? row[5] : "";
    teacher.phone = row[6] ? row[6] : "";
    return teacher;
}

std::vector<Teacher> CourseManager::getAllTeachers() {
    std::vector<Teacher> teachers;

    std::string sql = "SELECT id, user_id, teacher_id, name, department, email, phone FROM teachers ORDER BY id";

    auto result = db_->query(sql);
    if (!result) {
        return teachers;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        Teacher teacher;
        teacher.id = std::stoi(row[0]);
        teacher.user_id = std::stoi(row[1]);
        teacher.teacher_id = row[2] ? row[2] : "";
        teacher.name = row[3] ? row[3] : "";
        teacher.department = row[4] ? row[4] : "";
        teacher.email = row[5] ? row[5] : "";
        teacher.phone = row[6] ? row[6] : "";
        teachers.push_back(teacher);
    }

    return teachers;
}

std::vector<Course> CourseManager::getTeacherCourses(int teacherId) {
    std::vector<Course> courses;

    std::stringstream ss;
    ss << "SELECT id, course_code, course_name, teacher_id, description, credits, "
       << "max_students, current_students, semester, classroom, schedule, status "
       << "FROM courses WHERE teacher_id = " << teacherId << " ORDER BY id";

    auto result = db_->query(ss.str());
    if (!result) {
        return courses;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        Course course;
        course.id = std::stoi(row[0]);
        course.course_code = row[1] ? row[1] : "";
        course.course_name = row[2] ? row[2] : "";
        course.teacher_id = row[3] && std::string(row[3]).length() > 0 ? std::stoi(row[3]) : 0;
        course.teacher_name = ""; // 这里可以后续获取
        course.description = row[4] ? row[4] : "";
        course.credits = row[5] && std::string(row[5]).length() > 0 ? std::stoi(row[5]) : 0;
        course.max_students = row[6] && std::string(row[6]).length() > 0 ? std::stoi(row[6]) : 0;
        course.current_students = row[7] && std::string(row[7]).length() > 0 ? std::stoi(row[7]) : 0;
        course.semester = row[8] ? row[8] : "";
        course.classroom = row[9] ? row[9] : "";
        course.schedule = row[10] ? row[10] : "";
        course.status = row[11] ? row[11] : "";
        courses.push_back(course);
    }

    return courses;
}

std::vector<CourseSelection> CourseManager::getAllCourseSelections() {
    std::vector<CourseSelection> selections;

    std::string sql = R"(
        SELECT cs.id, cs.student_id, cs.course_id, s.name as student_name,
               c.course_name, cs.selection_time, cs.status
        FROM course_selections cs
        JOIN students s ON cs.student_id = s.id
        JOIN courses c ON cs.course_id = c.id
        ORDER BY cs.selection_time DESC
    )";

    auto result = db_->query(sql);
    if (!result) {
        return selections;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        CourseSelection selection;
        selection.id = std::stoi(row[0]);
        selection.student_id = std::stoi(row[1]);
        selection.course_id = std::stoi(row[2]);
        selection.student_name = row[3] ? row[3] : "";
        selection.course_name = row[4] ? row[4] : "";
        selection.selection_time = row[5] ? row[5] : "";
        selection.status = row[6] ? row[6] : "";
        selections.push_back(selection);
    }

    return selections;
}

std::vector<CourseSelection> CourseManager::getCourseSelectionsByCourseId(int courseId) {
    std::vector<CourseSelection> selections;

    std::stringstream ss;
    ss << R"(
        SELECT cs.id, cs.student_id, cs.course_id, s.name as student_name,
               c.course_name, cs.selection_time, cs.status
        FROM course_selections cs
        JOIN students s ON cs.student_id = s.id
        JOIN courses c ON cs.course_id = c.id
        WHERE cs.course_id = )" << courseId << R"(
        AND cs.status = '已选'
        ORDER BY cs.selection_time
    )";

    auto result = db_->query(ss.str());
    if (!result) {
        return selections;
    }

    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result.get()))) {
        CourseSelection selection;
        selection.id = std::stoi(row[0]);
        selection.student_id = std::stoi(row[1]);
        selection.course_id = std::stoi(row[2]);
        selection.student_name = row[3] ? row[3] : "";
        selection.course_name = row[4] ? row[4] : "";
        selection.selection_time = row[5] ? row[5] : "";
        selection.status = row[6] ? row[6] : "";
        selections.push_back(selection);
    }

    return selections;
}

bool CourseManager::selectCourse(int studentId, int courseId) {
    // 检查课程是否存在且可用
    if (!isCourseAvailable(courseId)) {
        std::cout << "Course " << courseId << " is not available" << std::endl;
        return false;
    }

    // 检查学生是否已经选了这门课
    if (hasStudentSelectedCourse(studentId, courseId)) {
        std::cout << "Student " << studentId << " has already selected course " << courseId << std::endl;
        return false;
    }

    // 检查是否曾经退选过这门课，如果是则更新记录，否则插入新记录
    std::stringstream ss;
    ss << "SELECT COUNT(*) FROM course_selections WHERE student_id = " << studentId
       << " AND course_id = " << courseId << " AND status = '退选'";

    auto result = db_->query(ss.str());
    if (!result) {
        return false;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    bool hasDroppedRecord = (row && std::stoi(row[0]) > 0);

    if (hasDroppedRecord) {
        // 更新已存在的退选记录为已选
        ss.str("");
        ss << "UPDATE course_selections SET status = '已选', selection_time = NOW() "
           << "WHERE student_id = " << studentId << " AND course_id = " << courseId << " AND status = '退选'";
    } else {
        // 插入新的选课记录
        ss.str("");
        ss << "INSERT INTO course_selections (student_id, course_id) VALUES (" << studentId << ", " << courseId << ")";
    }

    if (!db_->execute(ss.str())) {
        return false;
    }

    // 更新课程当前学生数，并检查是否达到最大人数
    ss.str("");
    ss << "UPDATE courses SET current_students = LEAST(max_students, current_students + 1), "
       << "status = CASE WHEN current_students + 1 >= max_students THEN '已满' ELSE '可选' END "
       << "WHERE id = " << courseId;
    if (!db_->execute(ss.str())) {
        return false;
    }

    return true;
}

bool CourseManager::dropCourse(int studentId, int courseId) {
    // 检查学生是否真的选了这门课
    if (!hasStudentSelectedCourse(studentId, courseId)) {
        return false; // 没有选课记录，无法退课
    }

    // 更新选课记录状态为"退选"
    std::stringstream ss;
    ss << "UPDATE course_selections SET status = '退选' WHERE student_id = " << studentId
       << " AND course_id = " << courseId << " AND status = '已选'";

    if (!db_->execute(ss.str())) {
        return false;
    }

    // 更新课程当前学生数
    ss.str("");
    ss << "UPDATE courses SET current_students = GREATEST(0, current_students - 1) WHERE id = " << courseId;
    if (!db_->execute(ss.str())) {
        return false;
    }

    // 更新课程状态
    ss.str("");
    ss << "UPDATE courses SET status = '可选' WHERE id = " << courseId << " AND current_students < max_students";
    db_->execute(ss.str());

    return true;
}

bool CourseManager::hasStudentSelectedCourse(int studentId, int courseId) {
    std::stringstream ss;
    ss << "SELECT COUNT(*) FROM course_selections WHERE student_id = " << studentId
       << " AND course_id = " << courseId << " AND status = '已选'";

    auto result = db_->query(ss.str());
    if (!result) {
        return false;
    }

    MYSQL_ROW row = mysql_fetch_row(result.get());
    if (!row) {
        return false;
    }

    return std::stoi(row[0]) > 0;
}

bool CourseManager::isCourseAvailable(int courseId) {
    auto course = getCourseById(courseId);
    if (!course) {
        return false;
    }

    return course->status == "可选" && course->current_students < course->max_students;
}